// ******************************************************************
// * Copyright(c)  Steven Lu
// * Description   : Entity_SALES_WORKING_PLAN
// * CreateDate    : 2011/9/15 23:42:05
// * Creater       : CodeSmith
// * LastChangeDate: 2011/9/15 23:42:05
// * LastChanger   : 2011/9/15 23:42:05
// * Version Info  : 1.0 For ESOL
// ******************************************************************

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Suzsoft.Smart.Data;
using ESOL.Common.Entity;

namespace ESOL.Business.Sales
{
/// <summary>
    /// SalesWorkingPlan
    /// </summary>
    public partial class SalesWorkingPlanBs
    {
        #region GetByID
        /// <summary>
        /// Get Entity
        /// </summary>
        /// <param name="objSalesWorkingPlanEntity">Entity</param>
        /// <returns>Entity</returns>
        public static SalesWorkingPlanEntity GetSalesWorkingPlan(SalesWorkingPlanEntity objSalesWorkingPlanEntity)
        {
            return DataAccess.SelectSingle<SalesWorkingPlanEntity>(objSalesWorkingPlanEntity);
        }
        public static List<SalesWorkingPlanEntity> GetSalesWorkingPlanList(string personId, DateTime date)
        {
            string sql = @"SELECT * 
                            FROM SALES_WORKING_PLAN
                            WHERE PERSON_ID = '" + personId + @"'
                            AND CONVERT(DATETIME,'" + date.ToString("yyyy-MM-dd") + @" 23:59:59') >= START_DATE 
                            AND CONVERT(DATETIME,'" + date.ToString("yyyy-MM-dd") + @"') <= END_DATE";
            return DataAccess.Select<SalesWorkingPlanEntity>(sql);
        }
        public static DataSet GetSalesWorkingPlanDataSet(string personId, DateTime date)
        {
            string sql = @"SELECT SWP.*, P.NAME_CN, '" + date.ToString("yyyy-MM-dd") + @"' AS WORKING_DATE
                            FROM SALES_WORKING_PLAN SWP
							LEFT JOIN MST_PERSON P ON P.PERSON_ID = SWP.PERSON_ID
                            WHERE SWP.PERSON_ID = '" + personId + @"'
                            AND CONVERT(DATETIME,'" + date.ToString("yyyy-MM-dd") + @" 23:59:59') >= START_DATE 
                            AND CONVERT(DATETIME,'" + date.ToString("yyyy-MM-dd") + @"') <= END_DATE";
            return DataAccess.SelectDataSet(sql);
        }
        #endregion

        #region Insert
        /// <summary>
        /// Insert Data
        /// </summary>
        /// <param name="objSalesWorkingPlanEntity">Entity</param>
        /// <returns>true Insert Succeed; false Insert Fail</returns>
        public static bool InsertSalesWorkingPlan(SalesWorkingPlanEntity objSalesWorkingPlanEntity)
        {
            return DataAccess.Insert(objSalesWorkingPlanEntity);
        }

        public static bool InsertSalesWorkingPlan(List<SalesWorkingPlanEntity> objSalesWorkingPlanEntityList)
        {
            return DataAccess.Insert(objSalesWorkingPlanEntityList);
        }
        #endregion

        #region Update
        /// <summary>
        /// Update Data
        /// </summary>
        /// <param name="objSalesWorkingPlanEntity">Entity</param>
        /// <returns>true Update Succeed; false Update Fail</returns>
        public static bool UpdateSalesWorkingPlan(SalesWorkingPlanEntity objSalesWorkingPlanEntity)
        {
            return DataAccess.Update(objSalesWorkingPlanEntity);
        }

        public static bool UpdateSalesWorkingPlan(List<SalesWorkingPlanEntity> objSalesWorkingPlanEntityList)
        {
            return DataAccess.Update<SalesWorkingPlanEntity>(objSalesWorkingPlanEntityList);
        }
        #endregion

        #region Delete
        /// <summary>
        /// Delete Data
        /// </summary>
        /// <param name="objSalesWorkingPlanEntity">Entity</param>
        /// <returns>true Delete Succeed; false Delete Fail</returns>
        public static bool DeleteSalesWorkingPlan(SalesWorkingPlanEntity objSalesWorkingPlanEntity)
        {
            return DataAccess.Delete(objSalesWorkingPlanEntity);
        }

        public static bool DeleteSalesWorkingPlan(List<SalesWorkingPlanEntity> objSalesWorkingPlanEntityList)
        {
            return DataAccess.Delete<SalesWorkingPlanEntity>(objSalesWorkingPlanEntityList);
        }
        #endregion

        #region GetAll
        /// <summary>
        /// Get All Data
        /// </summary>
        /// <returns>Back All Data<returns>
        public static DataSet GetSalesWorkingPlanAll()
        {
            return DataAccess.SelectAllDataSet<SalesWorkingPlanEntity>();
        }
        /// <summary>
        /// Get All Data List
        /// </summary>
        /// <returns>Back All Data List<returns>
        public static List<SalesWorkingPlanEntity> GetSalesWorkingPlanAllList()
        {
            return DataAccess.SelectAll<SalesWorkingPlanEntity>();
        }
        private static string GetObjectValue(object obj)
        {
            if (obj == null || obj.ToString() == "")
            {
                return "";
            }
            else
            {
                return "\n" + obj.ToString();
            }
        }
        public static DataTable GetDataForOverView(string personId, DateTime monday)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("PersonId", typeof(string));
            dt.Columns.Add("PersonName", typeof(string));
            dt.Columns.Add("Monday", typeof(string));
            dt.Columns.Add("Tuesday", typeof(string));
            dt.Columns.Add("Wednesday", typeof(string));
            dt.Columns.Add("Thursday", typeof(string));
            dt.Columns.Add("Friday", typeof(string));
            dt.Columns.Add("Saturday", typeof(string));
            dt.Columns.Add("Sunday", typeof(string));

//            string sql = @"SELECT P.PERSON_ID,P.NAME_CN,
//	                            CASE WHEN WP.START_DATE <= CONVERT(DATETIME,'" + monday.ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.ToString("yyyy-MM-dd") + @" 00:00:00') <= WP.END_DATE THEN WP.PLAN_TITLE
//	                            ELSE '' END  AS MONDAY,
//	                            CASE WHEN WP.START_DATE <= CONVERT(DATETIME,'" + monday.AddDays(1).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(1).ToString("yyyy-MM-dd") + @" 00:00:00') <= WP.END_DATE THEN WP.PLAN_TITLE
//	                            ELSE '' END  AS TUESDAY,
//	                            CASE WHEN WP.START_DATE <= CONVERT(DATETIME,'" + monday.AddDays(2).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(2).ToString("yyyy-MM-dd") + @" 00:00:00') <= WP.END_DATE THEN WP.PLAN_TITLE
//	                            ELSE '' END  AS WEDNESDAY,
//	                            CASE WHEN WP.START_DATE <= CONVERT(DATETIME,'" + monday.AddDays(3).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(3).ToString("yyyy-MM-dd") + @" 00:00:00') <= WP.END_DATE THEN WP.PLAN_TITLE
//	                            ELSE '' END  AS THURSDAY,
//	                            CASE WHEN WP.START_DATE <= CONVERT(DATETIME,'" + monday.AddDays(4).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(4).ToString("yyyy-MM-dd") + @" 00:00:00') <= WP.END_DATE THEN WP.PLAN_TITLE
//	                            ELSE '' END  AS FRIDAY,
//	                            CASE WHEN WP.START_DATE <= CONVERT(DATETIME,'" + monday.AddDays(5).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(5).ToString("yyyy-MM-dd") + @" 00:00:00') <= WP.END_DATE THEN WP.PLAN_TITLE
//	                            ELSE '' END  AS SATURDAY,
//	                            CASE WHEN WP.START_DATE <= CONVERT(DATETIME,'" + monday.AddDays(6).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(6).ToString("yyyy-MM-dd") + @" 00:00:00') <= WP.END_DATE THEN WP.PLAN_TITLE
//	                            ELSE '' END  AS SUNDAY
//                            FROM SALES_WORKING_PLAN WP
//                            INNER JOIN MST_PERSON P ON P.PERSON_ID = WP.PERSON_ID
//                            WHERE (WP.START_DATE >= CONVERT(DATETIME,'" + monday.ToString("yyyy-MM-dd") + @" 00:00:00') AND WP.START_DATE <= CONVERT(DATETIME,'" + monday.AddDays(6).ToString("yyyy-MM-dd") + @" 23:59:59')
//                            OR WP.END_DATE >= CONVERT(DATETIME,'" + monday.ToString("yyyy-MM-dd") + @" 00:00:00') AND WP.END_DATE <= CONVERT(DATETIME,'" + monday.AddDays(6).ToString("yyyy-MM-dd") + @" 23:59:59'))
//                            AND P.PERSON_ID = '" + personId + "'";
            string sqlNew = @"SELECT P.PERSON_ID,P.NAME_CN,
	                            CASE WHEN SUS.APP_START <= CONVERT(DATETIME,'" + monday.ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.ToString("yyyy-MM-dd") + @" 00:00:00') < SUS.APP_END THEN SUS.APP_LABEL
	                            ELSE '0' END  AS MONDAY,
	                            CASE WHEN SUS.APP_START <= CONVERT(DATETIME,'" + monday.AddDays(1).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(1).ToString("yyyy-MM-dd") + @" 00:00:00') < SUS.APP_END THEN SUS.APP_SUBJECT
	                            ELSE '0' END  AS TUESDAY,
	                            CASE WHEN SUS.APP_START <= CONVERT(DATETIME,'" + monday.AddDays(2).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(2).ToString("yyyy-MM-dd") + @" 00:00:00') < SUS.APP_END THEN SUS.APP_SUBJECT
	                            ELSE '0' END  AS WEDNESDAY,
	                            CASE WHEN SUS.APP_START <= CONVERT(DATETIME,'" + monday.AddDays(3).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(3).ToString("yyyy-MM-dd") + @" 00:00:00') < SUS.APP_END THEN SUS.APP_SUBJECT
	                            ELSE '0' END  AS THURSDAY,
	                            CASE WHEN SUS.APP_START <= CONVERT(DATETIME,'" + monday.AddDays(4).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(4).ToString("yyyy-MM-dd") + @" 00:00:00') < SUS.APP_END THEN SUS.APP_SUBJECT
	                            ELSE '0' END  AS FRIDAY,
	                            CASE WHEN SUS.APP_START <= CONVERT(DATETIME,'" + monday.AddDays(5).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(5).ToString("yyyy-MM-dd") + @" 00:00:00') < SUS.APP_END THEN SUS.APP_SUBJECT
	                            ELSE '0' END  AS SATURDAY,
	                            CASE WHEN SUS.APP_START <= CONVERT(DATETIME,'" + monday.AddDays(6).ToString("yyyy-MM-dd") + @" 23:59:59') AND CONVERT(DATETIME,'" + monday.AddDays(6).ToString("yyyy-MM-dd") + @" 00:00:00') < SUS.APP_END THEN SUS.APP_SUBJECT
	                            ELSE '0' END  AS SUNDAY
                            FROM dbo.SALES_USER_SCHEDULER SUS
                            INNER JOIN MST_PERSON P ON P.PERSON_ID = SUS.PERSON_ID
                            WHERE (SUS.APP_START >= CONVERT(DATETIME,'" + monday.ToString("yyyy-MM-dd") + @" 00:00:00') AND SUS.APP_START <= CONVERT(DATETIME,'" + monday.AddDays(6).ToString("yyyy-MM-dd") + @" 23:59:59')
                            OR  SUS.APP_END >= CONVERT(DATETIME,'" + monday.ToString("yyyy-MM-dd") + @" 00:00:00') AND  SUS.APP_END <= CONVERT(DATETIME,'" + monday.AddDays(6).ToString("yyyy-MM-dd") + @" 23:59:59'))
                            AND P.PERSON_ID = '" + personId + "'";

            DataSet ds = DataAccess.SelectDataSet(sqlNew);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                DataRow row = dt.NewRow();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    row["PersonId"] = dr["PERSON_ID"];
                    row["PersonName"] = dr["NAME_CN"];
                    row["Monday"] = row["Monday"].ToString() + GetObjectValue(dr["MONDAY"]);
                    row["Tuesday"] = row["Tuesday"].ToString() + GetObjectValue(dr["TUESDAY"]);
                    row["Wednesday"] = row["Wednesday"].ToString() + GetObjectValue(dr["WEDNESDAY"]);
                    row["Thursday"] = row["Thursday"].ToString() + GetObjectValue(dr["THURSDAY"]);
                    row["Friday"] = row["Friday"].ToString() + GetObjectValue(dr["FRIDAY"]);
                    row["Saturday"] = row["Saturday"].ToString() + GetObjectValue(dr["SATURDAY"]);
                    row["Sunday"] = row["Sunday"].ToString() + GetObjectValue(dr["SUNDAY"]);

                }
                dt.Rows.Add(row);
                foreach (DataColumn col in dt.Columns)
                {
                    if (dt.Rows[0][col].ToString().IndexOf("\n") == 0)
                    {
                        dt.Rows[0][col] = dt.Rows[0][col].ToString().Substring(1);
                    }
                }

                dt.AcceptChanges();
            }
            else
            {
                MstPersonEntity person = new MstPersonEntity();
                person.PersonId = personId;
                person = DataAccess.SelectSingle<MstPersonEntity>(person);
                DataRow row = dt.NewRow();
                row["PersonId"] = personId;
                row["PersonName"] = person.NameCn;
                dt.Rows.Add(row);
                dt.AcceptChanges();
            }
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dr[i].ToString().Trim() == "0")
                    {
                        dr[i] = null;
                    }
                }
            }
            return dt;
        }
        #endregion
	}
	
	
}	

